﻿using System.Collections.Generic;
using System.Data;
using System.Linq;
using CoreEx.Database.Client;
using CoreEx.Database.Metadata;
using LinFu.IoC.Configuration;
using LinFu.IoC.Interfaces;
using LinFu.IoC;

namespace CoreEx.Database.MetaData.Implementation
{
    [Implements(typeof(IMetadataRepositoryLoader))]
    public class MetadataRepositoryLoader : IMetadataRepositoryLoader, IInitialize
    {
                

        private readonly string _connectionName;
        
        private IDbConnection _dbConnection;

        public MetadataRepositoryLoader(string connectionName)
        {
            _connectionName = connectionName;
        }

        public IEnumerable<TableInfo> LoadTables()
        {                              
            return CreateTables();
        }

        /// <summary>
        /// Gets or sets the <see cref="ITypeMapper"/> used to map database-specific data types to CLR types.
        /// </summary>
        /// <value></value>
        public ITypeMapper TypeMapper { get; set; }

        /// <summary>
        /// Gets or sets the <see cref="IMetadataProvider"/> that is used to retrieve the metadata from the database.
        /// </summary>
        /// <value></value>
        public IMetadataProvider MetadataProvider { get; set; }


        private IEnumerable<TableInfo> CreateTables()
        {
            List<TableInfo> repository = new List<TableInfo>();

            var tables =
                MetadataProvider.GetTables().OrderBy(t => t.TableSchema).ThenBy(t => t.TableName);

            var columns =
                MetadataProvider.GetColumns();
            

            foreach (var basicTableInfo in tables)
            {
                TableInfo tableInfo = new TableInfo();
                
                tableInfo.TableSchema = basicTableInfo.TableSchema;
                tableInfo.TableName = basicTableInfo.TableName;
                tableInfo.TableCatalog = basicTableInfo.TableCatalog;
                tableInfo.TableType = basicTableInfo.TableType == "TABLE" ? TableType.Table : TableType.View;
                var tableColumns = columns.Where(
                    c => c.TableName == tableInfo.TableName && c.TableSchema == tableInfo.TableSchema && tableInfo.TableCatalog == c.TableCatalog)
                    .OrderBy(c => c.OrdinalPosition);

                foreach (var tableColumn in tableColumns)
                {
                    ColumnInfo columnInfo = new ColumnInfo();
                    columnInfo.Table = tableInfo;
                    columnInfo.ColumnName = tableColumn.ColumnName;
                    columnInfo.DataType = TypeMapper.MapType(tableColumn.DataTypeName, tableColumn.IsNullable);
                    columnInfo.OrdinalPosition = tableColumn.OrdinalPosition;
                    columnInfo.IsNullable = tableColumn.IsNullable;
                    tableInfo.Columns.Add(columnInfo.ColumnName,columnInfo);
                }

                repository.Add(tableInfo);

            }
            CreatePrimaryKeys(repository);
            CreateRelations(repository);
            return repository;
        }

        private void CreatePrimaryKeys(IEnumerable<TableInfo> tables)
        {
            var primaryKeys = MetadataProvider.GetPrimaryKeys();
            foreach (var tableInfo in tables)
            {
                var keyColumns =
                    primaryKeys.Where(
                        p =>
                        p.TableSchema == tableInfo.TableSchema && p.TableName == tableInfo.TableName &&
                        p.TableCatalog == tableInfo.TableCatalog).Join(tableInfo.Columns,k => k.ColumnName,c => c.Key,(keyColumn,column) => new {KeyColumn = keyColumn,Column = column.Value}).OrderBy(c => c.Column.OrdinalPosition);
                
                foreach (var keyColumn in keyColumns)
	            {
	                var keyInfo = tableInfo.PrimaryKey;
                    if (keyInfo == null)
                    {
                        keyInfo = new PrimaryKeyInfo();
                        keyInfo.Table = tableInfo;
                        keyInfo.Name = keyColumn.KeyColumn.ConstraintName;
                        tableInfo.PrimaryKey = keyInfo;
                    }
                    keyInfo.Columns.Add(keyColumn.Column);
              	}

            }
        }


        private void CreateRelations(IEnumerable<TableInfo> tables)
        {
            var foreignkeys = MetadataProvider.GetForeignKeys();
            IDictionary<string, RelationInfo> relations = new Dictionary<string, RelationInfo>();
            foreach (var tableInfo in tables)
            {
                //Find all foreign key columns and sort them by their constraint membership and ordinal position
                var keyColumns = foreignkeys.Where(
                    f =>
                    f.ForeignTableCatalog == tableInfo.TableCatalog && f.ForeignTableSchema == tableInfo.TableSchema &&
                    f.ForeignTableName == tableInfo.TableName)
                    .Join(tableInfo.Columns, k => k.ForeignColumnName, c => c.Key,
                          (keyColumn, column) => new {KeyColumn = keyColumn, Column = column.Value}).OrderBy(
                    c => c.KeyColumn.ConstraintName).ThenBy(c => c.Column.OrdinalPosition);

                foreach (var keyColumn in keyColumns)
                {
                    //Find the primary table that this foreignkey column refers to.
                    var primaryTableInfo =
                        tables.Where(t => t.TableCatalog == keyColumn.KeyColumn.PrimaryTableCatalog
                                          && t.TableSchema == keyColumn.KeyColumn.PrimaryTableSchema &&
                                          t.TableName == keyColumn.KeyColumn.PrimaryTableName).
                            First();
                    if (!relations.ContainsKey(keyColumn.KeyColumn.ConstraintName))
                    {
                        var foreignKey = new ForeignKeyInfo();                        
                        foreignKey.Name = keyColumn.KeyColumn.ConstraintName;
                        foreignKey.Table = tableInfo;
                        foreignKey.PrimaryTable = primaryTableInfo;
                        RelationInfo relationInfo = new RelationInfo();
                        relationInfo.PrimaryKey = tableInfo.PrimaryKey;
                        relationInfo.ForeignKey = foreignKey;
                            
                        tableInfo.Relations.Add(relationInfo);
                        tableInfo.ForeignKeys.Add(foreignKey);


                        primaryTableInfo.Relations.Add(relationInfo);
                        relations.Add(keyColumn.KeyColumn.ConstraintName,relationInfo);
                    }
                    ForeignKeyColumnInfo foreignKeyColumnInfo = new ForeignKeyColumnInfo();
                    foreignKeyColumnInfo.ForeignColumnInfo = keyColumn.Column;
                    foreignKeyColumnInfo.PrimaryColumnInfo =
                        primaryTableInfo.Columns[keyColumn.KeyColumn.PrimaryColumnName];
                    foreignKeyColumnInfo.ForeignKey = relations[keyColumn.KeyColumn.ConstraintName].ForeignKey;
                    relations[keyColumn.KeyColumn.ConstraintName].ForeignKey.Columns.Add(foreignKeyColumnInfo);
                }

            }      
        }

        public void Initialize(IServiceContainer source)
        {            
            string providerName = source.GetService<DbConnectionInfo>(_connectionName).ProviderName;                                    
            _dbConnection = source.GetService<IDbConnection>(_connectionName);            
            MetadataProvider = source.GetService<IMetadataProvider>(providerName);
            MetadataProvider.DbConnection = _dbConnection;
            TypeMapper = source.GetService<ITypeMapper>(providerName);
        }



    }
}
